package com.carple.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import com.carple.dto.Board;
import com.carple.dto.Member;
import com.carple.dto.Route;

public class RouteDao {
	
	public void insertRouteDeparture(Route route) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = "INSERT INTO route (routeorder, route, emailid, routeX, routeY) VALUES (?, ?, ?, ?, ?);";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, Integer.parseInt(route.getRouteOrder()));
			pstmt.setString(2, route.getCoordinates());
			pstmt.setString(3, route.getEmailId());
			pstmt.setString(4, route.getCoordinatesX());
			pstmt.setString(5, route.getCoordinatesY());
			
			pstmt.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { pstmt.close(); } catch (Exception ex) {ex.printStackTrace();}
			try { conn.close(); } catch (Exception ex) {ex.printStackTrace();}
		}
	}
	
	public void insertRouteDestination(Route route) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = "INSERT INTO route (routeorder, route, emailid) VALUES (?, ?, ?);";			
			pstmt=conn.prepareStatement(sql);		
			pstmt.setInt(1, Integer.parseInt(route.getRouteOrder()));
			pstmt.setString(2, route.getCoordinates());
			pstmt.setString(3, route.getEmailId());	
			
			pstmt.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { pstmt.close(); } catch (Exception ex) {ex.printStackTrace();}
			try { conn.close(); } catch (Exception ex) {ex.printStackTrace();}
		}
	}

	public ArrayList<Route> getRouteByEmailId(String emailId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
		ArrayList<Route> routes = new ArrayList<Route>();
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
			System.out.println(emailId);
				sql.append("SELECT route, routeorder  FROM route WHERE emailid = ? ORDER BY routeorder ASC");
				
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setString(1, emailId);
				rs = pstmt.executeQuery();
			
			
			
			while(rs.next()){
				Route route = new Route();				
				route.setCoordinates(rs.getString(1));
				route.setRouteOrder(rs.getString(2));
				routes.add(route);
				
			}
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return routes;
	}
	
	//메서드 이름은 NearMe이지만 메서드 내용은 전체 이용자 경로중 1번 경로를 갖고 오는 역할을 함.
	public ArrayList<Route> getRouteForNearMe() {
		 
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
		ArrayList<Route> routes = new ArrayList<Route>();
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
				sql.append("SELECT emailid, route FROM route WHERE routeorder = 1;");
				pstmt = conn.prepareStatement(sql.toString());
				rs = pstmt.executeQuery();
			
			while(rs.next()){
				Route route = new Route();
				route.setEmailId(rs.getString(1));				
				route.setCoordinates(rs.getString(2));
				routes.add(route);
				
			}
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return routes;
	}

	public Route getRouteOfMine(String emailId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Route> routes = new ArrayList<Route>();
		Route route = new Route();		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
				sql.append("SELECT emailid, route FROM route WHERE emailid = ? ;");
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setString(1, emailId);
				rs = pstmt.executeQuery();
			
			if(rs.next()){
				route.setEmailId(rs.getString(1));
				route.setCoordinates(rs.getString(2));
			}
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return route;
	}


	
	//(X좌표, Y좌표, 범위(킬로))
	public List<Route> getRangeCoordinate(String X, String Y, int range){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Route> routes = new ArrayList<Route>();
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
			sql.append("SELECT emailid, routeX, routeY ")
			   .append("FROM route ")
			   .append("WHERE (6371*acos(cos(radians(?))*cos(radians(routeX))")
			   .append("*cos(radians(routeY)-radians(?))")
			   .append("+sin(radians(?))*sin(radians(routeX))))")
			   .append(" < ? ")
			   .append("AND routeorder = 1");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setDouble(1, Double.parseDouble(X));
			pstmt.setDouble(2, Double.parseDouble(Y));
			pstmt.setDouble(3, Double.parseDouble(X));
			pstmt.setInt(4, range);
			rs = pstmt.executeQuery();
		
			while(rs.next()){
				Route route = new Route();
				route.setEmailId(rs.getString(1));
				route.setCoordinatesX(rs.getString(2));
				route.setCoordinatesY(rs.getString(3));
				route.setCoordinates(route.getCoordinatesX()+","+route.getCoordinatesY());
				route.setRouteOrder("1");;
				routes.add(route);
			}		
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		return routes;
	}
	
	
	
	
	//EmailID로 출발지 좌표(Route 객체 하나)만 뽑기
	public Route getOrderOneByEmailId(String emailId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
		Route route = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = "SELECT emailid, routeX , routeY "
					+ "FROM route "
					+ "WHERE emailid = ? AND routeorder = ?";
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setString(1, emailId);
				pstmt.setInt(2, 1);
				rs = pstmt.executeQuery();			
			if(rs.next()){
				route = new Route();				
				route.setEmailId(rs.getString(1));
				route.setCoordinatesX(rs.getString(2));
				route.setCoordinatesY(rs.getString(3));
				route.setCoordinates(route.getCoordinatesX()+","+route.getCoordinatesY());
				route.setRouteOrder("1");;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}	
		return route;
	}
}
